-- @author lixiyong01
-- @date 2023.03.14
-- 健康分汇总-存储/任务

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score_cube partition (dt=${dateSuffix})
select
    concat_ws(',',
        if(tag is null,null,'tag'),
        if(project is null,null,'project'),
        if(team is null,null,'team'),
        if(owner_id is null,null,'owner_id')
    ) as dimension,
    tag,
    project,
    team,
    owner_id,
    round(sum(power(weight + 1,1 / 3)* health_score)/ sum(power(weight + 1,1 / 3)),2) as total_score,
    '-' as health_reason,
    '-' as health_content
from hdp_lbg_supin_zplisting.app_zp_rock_health_score_appid
where dt='${#date(0, 0, -1):yyyyMMdd#}'
group by tag, project, team, owner_id
grouping sets(
    (tag, project),
    (tag, project, owner_id),
    (tag, team),
    (tag, team, owner_id)
);
